const { Service } = require('egg')
const Response = require('../utils/Response')

// 教务模块
class teachingService extends Service {
  // 查看课程信息---分页
  async findAllCourse(courseInfo) {
    const { mysql } = this.app
    const offSet = courseInfo.page === '1' ? 0 : (Number(courseInfo.page) - 1) * Number(courseInfo.pageSize)
    const pageSize = Number(courseInfo.pageSize)
    const courseName = courseInfo?.courseName ? courseInfo.courseName : null
    const courseCode = courseInfo?.courseCode ? courseInfo.courseCode : null
    const courseStatus = courseInfo?.courseStatus ? courseInfo.courseStatus : null
    const total = await mysql.query(
      `SELECT COUNT(*) as cnt FROM curriculum 
      WHERE (cur_name LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
        AND (cur_enCode LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
        AND (cur_statusCode = ? OR ? IS NULL) `,
      [courseName, courseName, courseCode, courseCode, courseStatus, courseStatus]
    )
    const myCourseInfo = await mysql.query(
      `SELECT * FROM curriculum 
       WHERE (cur_name LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
         AND (cur_enCode LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
         AND (cur_statusCode = ? OR ? IS NULL) 
       LIMIT ? OFFSET ?`,
      [courseName, courseName, courseCode, courseCode, courseStatus, courseStatus, pageSize, offSet]
    )
    return {
      total: total[0].cnt,
      myCourseInfo,
    }
  }

  // 课程管理----新增
  /**
   * @param {*} courseInfo
   * @returns 1: code重复 2：name重复 3：失败 4： 成功
   */
  async addCourseInfo(courseInfo) {
    const { mysql } = this.app
    const { cur_enCode, cur_name, cur_weeks, cur_type, cur_price, cur_number, cur_statusCode, cur_statusName } =
      courseInfo
    // 判断课程编码与课程name唯一性
    let count = await mysql.query('SELECT COUNT(*) as cnt FROM curriculum WHERE cur_enCode = ?', [cur_enCode])
    if (count[0].cnt) return 1
    count = await mysql.query('SELECT COUNT(*) as cnt FROM curriculum WHERE cur_name = ?', [cur_name])
    if (count[0].cnt) return 2
    // 插入数据
    const result = await mysql.query(
      'INSERT INTO curriculum(cur_enCode, cur_name, cur_weeks, cur_type, cur_price, cur_number, cur_statusCode,cur_statusName) VALUES (?,?,?,?,?,?,?,?)',
      [cur_enCode, cur_name, cur_weeks, cur_type, cur_price, cur_number, cur_statusCode, cur_statusName]
    )
    return result ? 4 : 3
  }

  // 课程管理---编辑
  async editCourse(courseInfo) {
    const { mysql } = this.app
    const {
      cur_enCode,
      cur_name,
      cur_weeks,
      cur_type,
      cur_price,
      cur_number,
      cur_statusCode,
      cur_statusName,
      cur_code,
    } = courseInfo
    const result = await mysql.query(
      'UPDATE curriculum SET cur_enCode = ?, cur_name = ?, cur_weeks = ?, cur_type = ?, cur_number = ?, cur_statusCode = ?, cur_statusName = ?, cur_price = ? WHERE cur_code = ?',
      [cur_enCode, cur_name, cur_weeks, cur_type, cur_number, cur_statusCode, cur_statusName, cur_price, cur_code]
    )
    return result ? true : false
  }

  // 课程管理--- 删除
  async deleteCourse(code) {
    const { mysql } = this.app
    await mysql.query(`SET FOREIGN_KEY_CHECKS = 0`)
    const result = await mysql.query(` DELETE FROM curriculum WHERE cur_code = ?`, [code])
    await mysql.query(`SET FOREIGN_KEY_CHECKS = 1`)
    if (result) return true
  }

  // 教室管理----分页
  async getClassAllRoom(params) {
    const { mysql } = this.app
    const offSet = params.page === '1' ? 0 : (Number(params.page) - 1) * Number(params.pageSize)
    const pageSize = Number(params.pageSize)
    const total = await mysql.query(
      `SELECT COUNT(*) as cnt FROM class_room WHERE (class_room LIKE CONCAT('%', ?, '%') OR ? IS NULL)`,
      [params.name, params.name]
    )
    const classRoomList = await mysql.query(
      `SELECT * FROM class_room WHERE (class_room LIKE CONCAT('%', ?, '%') OR ? IS NULL) LIMIT ? OFFSET ?`,
      [params.name, params.name, pageSize, offSet]
    )
    return {
      total: total[0].cnt,
      classRoomList,
    }
  }

  // 教室管理----新增
  async addClassRoom(classRoomInfo) {
    const { mysql } = this.app
    const remarks = classRoomInfo?.remarks ? classRoomInfo.remarks : ''
    const result = await mysql.query(`INSERT INTO class_room(class_room, addRess, remarks) VALUES (?,?,?)`, [
      classRoomInfo.name,
      classRoomInfo.addRess,
      remarks,
    ])
    return result ? true : false
  }

  // 教室管理---编辑
  async editClassRoom(classRoomInfo) {
    const { mysql } = this.app
    const remarks = classRoomInfo?.remarks ? classRoomInfo.remarks : ''
    const result = await mysql.query(`UPDATE class_room SET remarks=? WHERE id=?`, [remarks, classRoomInfo.id])
    return result ? true : false
  }

  // 教室管理----删除
  async deleteClassRoom(params) {
    const { mysql } = this.app
    const result = await mysql.query(`DELETE FROM class_room WHERE id=?`, [params.id])
    return result ? true : false
  }

  // 排课计划 ---- 新增
  async addCourse(courseInfo) {
    const { mysql } = this.app
    const { cur_enCode, teacher, start_weeks, end_weeks, weekNum } = courseInfo
    const sql = `INSERT INTO
      time_table(course_id, teacher_id, start_weeks, end_weeks, weekNum)
      VALUES (?, ?, ?, ?, ?)`
    const sqlTime = `INSERT INTO
      class_time(cur_code, week, start_class, end_class,tea_code,class_room)
      VALUES (?, ?, ?, ?, ?, ?)`
    await mysql.query(sql, [cur_enCode, teacher, start_weeks, end_weeks, weekNum])
    const promises = courseInfo.classTime.map((item) => {
      const { week, startClassNum, endClassNum, classRoom } = item
      return mysql.query(sqlTime, [cur_enCode, week, startClassNum, endClassNum, teacher, classRoom])
    })
    const obj = {
      flag: true,
      successList: [],
      failList: [],
    }
    try {
      const results = await Promise.allSettled(promises)
      results.forEach((res, index) => {
        if (res.status === 'fulfilled' && res.value.affectedRows > 0) {
          obj.successList.push(++index)
        } else {
          obj.flag = false
          obj.failList.push(++index)
        }
      })
    } catch (err) {}
    return obj
  }

  // 排课计划 ---- 分页模糊查询
  async pageClassPlanList(params) {
    const { mysql } = this.app
    const offSet = params.page === '1' ? 0 : (Number(params.page) - 1) * Number(params.pageSize)
    const pageSize = Number(params.pageSize)
    const cur_name = params?.cur_name ? params.cur_name : null
    const totalSql = `SELECT COUNT(*) AS total_count
      FROM time_table JOIN curriculum ON time_table.course_id = curriculum.cur_code JOIN teachers ON time_table.teacher_id = teachers.tea_code
      WHERE (cur_name LIKE CONCAT ('%', ?, '%') OR ? IS NULL)`
    const sql = `SELECT time_table.id, cur_name, tea_name, tea_code, start_weeks, end_weeks, cur_code, time_table.weekNum
      FROM time_table JOIN curriculum ON time_table.course_id = curriculum.cur_code JOIN teachers ON time_table.teacher_id = teachers.tea_code
      WHERE (cur_name LIKE CONCAT ('%', ?, '%') OR ? IS NULL)
      LIMIT ? OFFSET ?`
    const total = await mysql.query(totalSql, [cur_name, cur_name])
    const list = await mysql.query(sql, [cur_name, cur_name, pageSize, offSet])
    const cur_list = list.map((item) => item.cur_code)
    const classTimeListSql = ` SELECT time.code, time.cur_code, time.week, time.start_class, time.end_class, time.class_room as room_code, room.class_room
      FROM class_time as time JOIN class_room as room ON time.class_room = room.id
      WHERE cur_code IN (${cur_list.join()})`
    const classTimeList = await mysql.query(classTimeListSql)
    list.forEach((item) => {
      item.classTimeList = classTimeList.filter((ite) => ite.cur_code === item.cur_code)
    })
    return {
      total: total[0].total_count,
      list,
    }
  }

  // 排课计划 --- 删除
  async deleteClassPlanList(params) {
    const { mysql } = this.app
    const { ctx } = this
    const result = await mysql.query('DELETE FROM time_table WHERE id=?', [params.id])
    await mysql.query('DELETE FROM class_time WHERE cur_code=?', [params.code])
    if (result.affectedRows > 0) {
      ctx.body = new Response('删除成功', 200, result)
    } else {
      throw new Response('删除失败', 500)
    }
  }

  // 排课计划 ---- 编辑
  async updateClassPlan(params) {
    const { ctx } = this
    const { mysql } = this.app
    const deleteSql = `DELETE FROM class_time WHERE cur_code=?`
    const sql = `INSERT INTO
      class_time(cur_code, week, start_class, end_class,tea_code,class_room)
      VALUES (?, ?, ?, ?, ?, ?)`
    try {
      await mysql.query(deleteSql, [params.cur_enCode])
      const promises = params.classTime.map((item) => {
        const { week, startClassNum, endClassNum, classRoom } = item
        return mysql.query(sql, [params.cur_enCode, week, startClassNum, endClassNum, params.teacher, classRoom])
      })
      const results = await Promise.allSettled(promises)
      const obj = {
        flag: true,
        successList: [],
        failList: [],
      }
      results.forEach((res, index) => {
        if (res.status === 'fulfilled' && res.value.affectedRows > 0) {
          obj.successList.push(++index)
        } else {
          obj.flag = false
          obj.failList.push(++index)
        }
      })
      ctx.body = new Response('编辑成功', 200, obj)
    } catch (err) {
      throw new Response('异常错误', 500, err)
    }
  }

  // 课程表 ---- 列表数据
  async queryClassTime(params) {
    const { ctx } = this
    const { mysql } = this.app
    const { cur_code, tea_code } = params
    const week = [1, 2, 3, 4, 5, 6, 7] // 一周七天
    const classNum = [1, 2, 3, 4, 5, 6] // 一天六节课
    const classInfoList = []
    const sql = `SELECT cur.cur_name, class_time.week, class_time.start_class, class_time.end_class, room.class_room,tea.tea_name
        FROM class_time
          JOIN curriculum as cur ON class_time.cur_code = cur.cur_code 
          JOIN teachers as tea ON class_time.tea_code = tea.tea_code
          JOIN class_room as room ON room.id = class_time.class_room
        WHERE (class_time.cur_code = ? OR ? IS NULL)
          AND (class_time.tea_code = ? OR ? IS NULL)`
    try {
      const result = await mysql.query(sql, [cur_code, cur_code, tea_code, tea_code])
      week.forEach((item, index) => {
        const arr = result.filter((ele) => ele.week === item)
        const objArr = []
        classNum.forEach((element) => {
          if (arr.some((item) => item.start_class === element || item.end_class === element)) {
            objArr.push(arr.find((target) => target.start_class === element || target.end_class === element))
          } else {
            objArr.push(null)
          }
        })
        classInfoList.push(objArr)
      })
      ctx.body = new Response('查询成功', 200, classInfoList)
    } catch (err) {
      throw new Response('异常错误', 500, err)
    }
  }

  // 课程表 ---- 已经排课的教师下拉
  async queryTeacherList() {
    const { ctx } = this
    const { mysql } = this.app
    const sql = `SELECT tea.tea_code, tea.tea_name
      FROM time_table as time
      JOIN teachers as tea ON tea.tea_code = time.teacher_id`
    try {
      const newArr = await mysql.query(sql)
      const result = Array.from(new Set(newArr.map((obj) => obj.tea_code))).map((code) =>
        newArr.find((obj) => obj.tea_code === code)
      )
      ctx.body = new Response('success', 200, result)
    } catch (err) {
      throw new Response('异常错误', 500, err)
    }
  }
}

module.exports = teachingService
